<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE topic
  PUBLIC "-//OASIS//DTD DITA Composite//EN" "ditabase.dtd">
<topic id="topic1"><title id="bx20941">CREATE OPERATOR CLASS</title><body><p id="sql_command_desc">Defines a new operator class.</p><section id="section2"><title>Synopsis</title><codeblock id="sql_command_synopsis">CREATE OPERATOR CLASS <varname>name</varname> [DEFAULT] FOR TYPE <varname>data_type</varname>  
  USING <varname>index_method</varname> [ FAMILY <varname>family_name</varname> ] AS 
  { OPERATOR <varname>strategy_number</varname> <varname>operator_name</varname> [ ( <varname>op_type</varname>, <varname>op_type</varname> ) ] [ FOR SEARCH | FOR ORDER BY <varname>sort_family_name</varname> ]
  | FUNCTION <varname>support_number</varname> <varname>funcname</varname> (<varname>argument_type</varname> [, ...] )
  | STORAGE <varname>storage_type</varname>
  } [, ... ]</codeblock></section><section id="section3"><title>Description</title><p><codeph>CREATE OPERATOR CLASS</codeph> creates a new operator class.
An operator class defines how a particular data type can be used with
an index. The operator class specifies that certain operators will fill
particular roles or strategies for this data type and this index method.
The operator class also specifies the support procedures to be used by
the index method when the operator class is selected for an index column.
All the operators and functions used by an operator class must be defined
before the operator class is created. Any functions used to implement
the operator class must be defined as <codeph>IMMUTABLE</codeph>.</p><p><codeph>CREATE OPERATOR CLASS</codeph> does not presently check whether
the operator class definition includes all the operators and functions
required by the index method, nor whether the operators and functions
form a self-consistent set. It is the user's responsibility to define
a valid operator class. </p><p>You must be a superuser to create an operator class.</p></section><section id="section4"><title>Parameters</title>
            <parml>
                <plentry>
                    <pt><varname>name</varname></pt>
                    <pd>The (optionally schema-qualified) name of the operator class to be defined.
                        Two operator classes in the same schema can have the same name only if they
                        are for different index methods. </pd>
                </plentry>
                <plentry>
                    <pt>DEFAULT</pt>
                    <pd>Makes the operator class the default operator class for its data type. At
                        most one operator class can be the default for a specific data type and
                        index method.</pd>
                </plentry>
                <plentry>
                    <pt><varname>data_type</varname></pt>
                    <pd>The column data type that this operator class is for. </pd>
                </plentry>
                <plentry>
                    <pt><varname>index_method</varname></pt>
                    <pd>The name of the index method this operator class is for. Choices are
                            <codeph>btree</codeph>, <codeph>bitmap</codeph>, and
                            <codeph>gist</codeph>. </pd>
                </plentry>
                <plentry>
                    <pt><varname>family_name</varname></pt>
                    <pd>The name of the existing operator family to add this operator class to. If
                        not specified, a family named the same as the operator class is used
                        (creating it, if it doesn't already exist). </pd>
                </plentry>
                <plentry>
                    <pt><varname>strategy_number</varname></pt>
                    <pd>The operators associated with an operator class are identified by
                            <i>strategy numbers</i>, which serve to identify the semantics of each
                        operator within the context of its operator class. For example, B-trees
                        impose a strict ordering on keys, lesser to greater, and so operators like
                            <i>less than</i> and <i>greater than or equal to</i> are interesting
                        with respect to a B-tree. These strategies can be thought of as generalized
                        operators. Each operator class specifies which actual operator corresponds
                        to each strategy for a particular data type and interpretation of the index
                        semantics. The corresponding strategy numbers for each index method are as
                            follows:<table id="bx145491">
                            <title>B-tree and Bitmap Strategies</title>
                            <tgroup cols="2">
                                <colspec colnum="1" colname="col1" colwidth="110pt"/>
                                <colspec colnum="2" colname="col2" colwidth="99pt"/>
                                <thead>
                                    <row>
                                        <entry colname="col1">Operation</entry>
                                        <entry colname="col2">Strategy Number</entry>
                                    </row>
                                </thead>
                                <tbody>
                                    <row>
                                        <entry colname="col1">less than</entry>
                                        <entry colname="col2">1</entry>
                                    </row>
                                    <row>
                                        <entry colname="col1">less than or equal</entry>
                                        <entry colname="col2">2</entry>
                                    </row>
                                    <row>
                                        <entry colname="col1">equal</entry>
                                        <entry colname="col2">3</entry>
                                    </row>
                                    <row>
                                        <entry colname="col1">greater than or equal</entry>
                                        <entry colname="col2">4</entry>
                                    </row>
                                    <row>
                                        <entry colname="col1">greater than</entry>
                                        <entry colname="col2">5</entry>
                                    </row>
                                </tbody>
                            </tgroup>
                        </table><table id="bx145491a">
                            <title>GiST Two-Dimensional Strategies (R-Tree)</title>
                            <tgroup cols="2">
                                <colspec colnum="1" colname="col1" colwidth="110pt"/>
                                <colspec colnum="2" colname="col2" colwidth="99pt"/>
                                <thead>
                                    <row>
                                        <entry colname="col1">Operation</entry>
                                        <entry colname="col2">Strategy Number</entry>
                                    </row>
                                </thead>
                                <tbody>
                                    <row>
                                        <entry colname="col1">strictly left of</entry>
                                        <entry colname="col2">1</entry>
                                    </row>
                                    <row>
                                        <entry colname="col1">does not extend to right of</entry>
                                        <entry colname="col2">2</entry>
                                    </row>
                                    <row>
                                        <entry colname="col1">overlaps</entry>
                                        <entry colname="col2">3</entry>
                                    </row>
                                    <row>
                                        <entry colname="col1">does not extend to left of</entry>
                                        <entry colname="col2">4</entry>
                                    </row>
                                    <row>
                                        <entry colname="col1">strictly right of</entry>
                                        <entry colname="col2">5</entry>
                                    </row>
                                    <row>
                                        <entry colname="col1">same</entry>
                                        <entry colname="col2">6</entry>
                                    </row>
                                    <row>
                                        <entry colname="col1">contains</entry>
                                        <entry colname="col2">7</entry>
                                    </row>
                                    <row>
                                        <entry colname="col1">contained by</entry>
                                        <entry colname="col2">8</entry>
                                    </row>
                                    <row>
                                        <entry colname="col1">does not extend above</entry>
                                        <entry colname="col2">9</entry>
                                    </row>
                                    <row>
                                        <entry colname="col1">strictly below</entry>
                                        <entry colname="col2">10</entry>
                                    </row>
                                    <row>
                                        <entry colname="col1">strictly above</entry>
                                        <entry colname="col2">11</entry>
                                    </row>
                                    <row>
                                        <entry colname="col1">does not extend below</entry>
                                        <entry colname="col2">12</entry>
                                    </row>
                                </tbody>
                            </tgroup>
                        </table></pd>
                </plentry>
                <plentry>
                    <pt><varname>sort_family_name</varname></pt>
                    <pd>The name (optionally schema-qualified) of an existing
                            <codeph>btree</codeph> operator family that describes the sort ordering
                        associated with an ordering operator.</pd>
                    <pd>If neither <codeph>FOR SEARCH</codeph> nor <codeph>FOR ORDER BY</codeph> is
                        specified, <codeph>FOR SEARCH</codeph> is the default. </pd>
                </plentry>
                <plentry>
                    <pt><varname>operator_name</varname></pt>
                    <pd>The name (optionally schema-qualified) of an operator associated with the
                        operator class. </pd>
                </plentry>
                <plentry>
                    <pt><varname>op_type</varname></pt>
                    <pd>In an <codeph>OPERATOR</codeph> clause, the operand data type(s) of the
                        operator, or <codeph>NONE</codeph> to signify a left-unary or right-unary
                        operator. The operand data types can be omitted in the normal case where
                        they are the same as the operator class's data type. </pd>
                    <pd>In a <codeph>FUNCTION</codeph> clause, the operand data type(s) the function
                        is intended to support, if different from the input data type(s) of the
                        function (for B-tree comparison functions and hash functions) or the class's
                        data type (for B-tree sort support functions and all functions in GiST,
                        SP-GiST, and GIN operator classes). These defaults are correct, and so
                            <varname>op_type</varname> need not be specified in
                            <codeph>FUNCTION</codeph> clauses, except for the case of a B-tree sort
                        support function that is meant to support cross-data-type comparisons.</pd>
                </plentry>
                <plentry>
                    <pt><varname>support_number</varname></pt>
                    <pd>Index methods require additional support routines in order to work. These
                        operations are administrative routines used internally by the index methods.
                        As with strategies, the operator class identifies which specific functions
                        should play each of these roles for a given data type and semantic
                        interpretation. The index method defines the set of functions it needs, and
                        the operator class identifies the correct functions to use by assigning them
                        to the <i>support function numbers</i> as follows:<table id="bx145974">
                            <title>B-tree and Bitmap Support Functions</title>
                            <tgroup cols="2">
                                <colspec colnum="1" colname="col1" colwidth="275pt"/>
                                <colspec colnum="2" colname="col2" colwidth="99pt"/>
                                <thead>
                                    <row>
                                        <entry colname="col1">Function</entry>
                                        <entry colname="col2">Support Number</entry>
                                    </row>
                                </thead>
                                <tbody>
                                    <row>
                                        <entry colname="col1">Compare two keys and return an integer
                                            less than zero, zero, or greater than zero, indicating
                                            whether the first key is less than, equal to, or greater
                                            than the second.</entry>
                                        <entry colname="col2">1</entry>
                                    </row>
                                </tbody>
                            </tgroup>
                        </table><table id="bx145974a">
                            <title>GiST Support Functions</title>
                            <tgroup cols="2">
                                <colspec colnum="1" colname="col1" colwidth="276pt"/>
                                <colspec colnum="2" colname="col2" colwidth="99pt"/>
                                <thead>
                                    <row>
                                        <entry colname="col1">Function</entry>
                                        <entry colname="col2">Support Number</entry>
                                    </row>
                                </thead>
                                <tbody>
                                    <row>
                                        <entry colname="col1">consistent - determine whether key
                                            satisfies the query qualifier.</entry>
                                        <entry colname="col2">1</entry>
                                    </row>
                                    <row>
                                        <entry colname="col1">union - compute union of a set of
                                            keys.</entry>
                                        <entry colname="col2">2</entry>
                                    </row>
                                    <row>
                                        <entry colname="col1">compress - compute a compressed
                                            representation of a key or value to be indexed.</entry>
                                        <entry colname="col2">3</entry>
                                    </row>
                                    <row>
                                        <entry colname="col1">decompress - compute a decompressed
                                            representation of a compressed key.</entry>
                                        <entry colname="col2">4</entry>
                                    </row>
                                    <row>
                                        <entry colname="col1">penalty - compute penalty for
                                            inserting new key into subtree with given subtree's
                                            key.</entry>
                                        <entry colname="col2">5</entry>
                                    </row>
                                    <row>
                                        <entry colname="col1">picksplit - determine which entries of
                                            a page are to be moved to the new page and compute the
                                            union keys for resulting pages.</entry>
                                        <entry colname="col2">6</entry>
                                    </row>
                                    <row>
                                        <entry colname="col1">equal - compare two keys and return
                                            true if they are equal.</entry>
                                        <entry colname="col2">7</entry>
                                    </row>
                                </tbody>
                            </tgroup>
                        </table></pd>
                </plentry>
                <plentry>
                    <pt><varname>funcname</varname></pt>
                    <pd>The name (optionally schema-qualified) of a function that is an index method
                        support procedure for the operator class. </pd>
                </plentry>
                <plentry>
                    <pt><varname>argument_types</varname></pt>
                    <pd>The parameter data type(s) of the function. </pd>
                </plentry>
                <plentry>
                    <pt><varname>storage_type</varname></pt>
                    <pd>The data type actually stored in the index. Normally this is the same as the
                        column data type, but some index methods (currently GiST and GIN) allow it
                        to be different. The <codeph>STORAGE</codeph> clause must be omitted unless
                        the index method allows a different type to be used. </pd>
                </plentry>
            </parml></section><section id="section5"><title>Notes</title><p>Because the index machinery does not check access permissions on functions
before using them, including a function or operator in an operator class
is the same as granting public execute permission on it. This is usually
not an issue for the sorts of functions that are useful in an operator
class. </p><p>The operators should not be defined by SQL functions. A SQL function
is likely to be inlined into the calling query, which will prevent the
optimizer from recognizing that the query matches an index.</p><p>Any functions used to implement the operator class must be defined
as <codeph>IMMUTABLE</codeph>.</p>
            <p>Before Greenplum Database 6.0, the <codeph>OPERATOR</codeph> clause could include a
                    <codeph>RECHECK</codeph> option. This option is no longer supported. Greenplum
                Database now determines whether an index operator is "lossy" on-the-fly at run time. This
                allows more efficient handling of cases where an operator might or might not be
                lossy.</p></section><section id="section6"><title>Examples</title><p>The following example command defines a GiST index operator class for the data type
                    <codeph>_int4</codeph> (array of int4). See the 
                <codeph>intarray</codeph> contrib module for the complete example.</p><codeblock>CREATE OPERATOR CLASS gist__int_ops
    DEFAULT FOR TYPE _int4 USING gist AS
        OPERATOR 3 &amp;&amp;,
        OPERATOR 6 = (anyarray, anyarray),
        OPERATOR 7 @&gt;,
        OPERATOR 8 &lt;@,
        OPERATOR 20 @@ (_int4, query_int),
        FUNCTION 1 g_int_consistent (internal, _int4, int, oid, internal),
        FUNCTION 2 g_int_union (internal, internal),
        FUNCTION 3 g_int_compress (internal),
        FUNCTION 4 g_int_decompress (internal),
        FUNCTION 5 g_int_penalty (internal, internal, internal),
        FUNCTION 6 g_int_picksplit (internal, internal),
        FUNCTION 7 g_int_same (_int4, _int4, internal);</codeblock></section><section id="section7"><title>Compatibility</title><p><codeph>CREATE OPERATOR CLASS</codeph> is a Greenplum Database extension.
There is no <codeph>CREATE OPERATOR CLASS</codeph> statement in the SQL
standard.</p></section><section id="section8"><title>See Also</title><p><codeph><xref href="ALTER_OPERATOR_CLASS.xml#topic1" type="topic" format="dita"/></codeph>,
                        <codeph><xref href="./DROP_OPERATOR_CLASS.xml#topic1" type="topic"
                        format="dita"/></codeph>, <codeph><xref href="CREATE_FUNCTION.xml#topic1"
                        type="topic" format="dita"/></codeph></p></section></body></topic>
